
package com.avrio.utils;

// <editor-fold defaultstate="collapsed" desc="import">
import com.avrio.json.JsonObject;

import java.io.IOException;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;

import java.util.logging.Level;
import java.util.logging.Logger;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;

import javax.servlet.http.HttpServletRequest;
// </editor-fold>

public class MySQL {

    private final static Logger log = Logger.getLogger(MySQL.class.getName());

    private final static String DEFAUL_DATE = "0000-00-00 00:00:00";

    private DataSource ds;

// <editor-fold defaultstate="collapsed" desc="MySQL()">

    public MySQL() {

        try {
            Context initCtx = new InitialContext();
            ds = (DataSource)initCtx.lookup("jdbc/mysqlcluster");
        }
        catch(NamingException e) {
            log.log(Level.SEVERE, "ds=null");
        }

        log.log(Level.INFO, "Init: mysql {0}", getVersion());
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getVersion()">

    private String getVersion() {

        String version = " Error";

        try(Connection conn = ds.getConnection();
            PreparedStatement us = conn.prepareStatement("SELECT version()");
            ResultSet rs = us.executeQuery()
        ) {
            if(rs.next()) {
                version = rs.getString("version()");
            }
        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "getVersion()", e);
        }

        return " Version is: " + version;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="GRUD Person Table">
// <editor-fold defaultstate="collapsed" desc="loadPerson()">

    final static String PERSON_LOAD =
        "SELECT SQL_CALC_FOUND_ROWS IFNULL(" +
        "CONCAT(" +
        "'{id:',id," +
        "',email:',QUOTE(email)," +
        "',version:',version," +
        "',drivers_license_num:',QUOTE(drivers_license_num)," +
        "',ssn:',ssn," +
        "',account_number:',QUOTE(account_number)," +
        "',name_prefix:',QUOTE(name_prefix)," +
        "',given_name:',QUOTE(given_name)," +
        "',middle_name:',QUOTE(middle_name)," +
        "',family_name:',QUOTE(family_name)," +
        "',name_suffix:',QUOTE(name_suffix)," +
        "',alias_name:',QUOTE(alias_name)," +
        "',description:',QUOTE(IFNULL(description,''))," +
        "',telephone1:',QUOTE(telephone1)," +
        "',telephone2:',QUOTE(telephone2)," +
        "',telephone3:',QUOTE(telephone3)," +
        "',gender_code:',QUOTE(gender_code)," +
        "',birth_time:',QUOTE(birth_time)," +
        "',multiple_birth_ind:',multiple_birth_ind," +
        "',birth_order:',birth_order," +
        "',deceased_ind:',deceased_ind," +
        "',deceased_datetime:',QUOTE(deceased_datetime)," +
        "',organ_doner_ind:',organ_doner_ind," +
        "',address_line1:',QUOTE(address_line1)," +
        "',address_line2:',QUOTE(address_line2)," +
        "',city:',QUOTE(city)," +
        "',state_code:',QUOTE(state_code)," +
        "',postal_code:',QUOTE(postal_code)," +
        "',country_code:',QUOTE(country_code)," +
        "',marital_status_code:',QUOTE(marital_status_code)," +
        "',occupation_code:',QUOTE(occupation_code)," +
        "',nationality:',QUOTE(nationality)," +
        "',citizen_role_type:',QUOTE(citizen_role_type)," +
        "',related_person:',related_person," +
        "',relationship_role_type:',QUOTE(relationship_role_type)," +
        "',birth_place:',QUOTE(birth_place)," +
        "',primary_language_code:',QUOTE(primary_language_code)," +
        "',race:',QUOTE(race)," +
        "',ethnicity:',QUOTE(ethnicity)," +
        "',education_level_code:',QUOTE(education_level_code)," +
        "',disability_type_code:',QUOTE(disability_type_code)," +
        "',living_arrangement_code:',QUOTE(living_arrangement_code)," +
        "',religious_affiliation_code:',QUOTE(religious_affiliation_code)," +
        "',mothers_maiden_name:',QUOTE(mothers_maiden_name)," +
        "',veterans_status:',QUOTE(veterans_status)," +
        "'}'),'{success:false}') json " +
        "FROM avrio.person " +
        "ORDER BY ";

    public String loadPerson(int start, int limit, String sort, String dir) {

        long set = System.currentTimeMillis();

        String vsort;
        switch(sort) {
            case "email":
                vsort = " email " + dir; break;
            default:
                vsort = " id ASC ";
        }

        StringBuilder result = new StringBuilder();
        int count = 0;
        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = conn.prepareStatement(PERSON_LOAD + vsort + " LIMIT ?,?");
            us.setInt(1, start);
            us.setInt(2, limit);

            ResultSet rs = us.executeQuery();
            for(int idx = 0; rs.next(); idx++) {
		if(idx != 0) result.append(',');
                result.append(rs.getString("json"));
            }
            //rs.close();
            //us.close();

            // items count
            us = conn.prepareStatement("SELECT FOUND_ROWS() cnt");
            rs = us.executeQuery();
            if(rs.next()) {
                count = rs.getInt("cnt");
            }
            rs.close();
            us.close();
        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "loadPerson()", e);
            return "{success:false,ex:'load',err:" + e + "}";
        }

        return "{success:true,ex:'load',total:" + count + ",time:" + (System.currentTimeMillis() - set) + ",data:[" + result.toString() + "]}";
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="updatePerson()">

    final static String PERSON_UPDATE =
        "UPDATE avrio.person SET " +
        "email=?,version=?,drivers_license_num=?,ssn=?,account_number=?,name_prefix=?," +
        "given_name=?,middle_name=?,family_name=?,name_suffix=?,alias_name=?," +
        "description=?,telephone1=?,telephone2=?,telephone3=?,gender_code=?,birth_time=?," +
        "multiple_birth_ind=?,birth_order=?,deceased_ind=?,deceased_datetime=?,organ_doner_ind=?," +
        "address_line1=?,address_line2=?,city=?,state_code=?,postal_code=?,country_code=?," +
        "marital_status_code=?,occupation_code=?,nationality=?,citizen_role_type=?," +
        "related_person=?,relationship_role_type=?,birth_place=?,primary_language_code=?,race=?," +
        "ethnicity=?,education_level_code=?,disability_type_code=?,living_arrangement_code=?," +
        "religious_affiliation_code=?,mothers_maiden_name=?,veterans_status=? " +
        "WHERE id=?";

    public String updatePerson(HttpServletRequest request) {

        long start = System.currentTimeMillis();
        try (Connection conn = ds.getConnection()) {

            JsonObject json = JsonObject.readFrom(request.getReader());
            System.out.println("updatePerson="+json);

            int pos = 1;
            PreparedStatement us = conn.prepareStatement(PERSON_UPDATE);
            us.setString( pos++, json.get("email").asString());
            us.setLong(   pos++, json.get("version").asLong());
            us.setString( pos++, json.get("drivers_license_num").asString());
            us.setInt(    pos++, json.get("ssn").asInt());
            us.setString( pos++, json.get("account_number").asString());
            us.setString( pos++, json.get("name_prefix").asString());
            us.setString( pos++, json.get("given_name").asString());
            us.setString( pos++, json.get("middle_name").asString());
            us.setString( pos++, json.get("family_name").asString());
            us.setString( pos++, json.get("name_suffix").asString());
            us.setString( pos++, json.get("alias_name").asString());
            us.setString( pos++, json.get("description").asString());
            us.setString( pos++, json.get("telephone1").asString());
            us.setString( pos++, json.get("telephone2").asString());
            us.setString( pos++, json.get("telephone3").asString());
            us.setString( pos++, json.get("gender_code").asString());
            us.setString( pos++, (json.get("birth_time") == null || json.get("birth_time").isNull()) ? DEFAUL_DATE : json.get("birth_time").asString());
            us.setBoolean(pos++, json.get("multiple_birth_ind").asBoolean());
            us.setInt(    pos++, json.get("birth_order").asInt());
            us.setBoolean(pos++, json.get("deceased_ind").asBoolean());
            us.setString( pos++, (json.get("deceased_datetime") == null || json.get("deceased_datetime").isNull()) ? DEFAUL_DATE : json.get("deceased_datetime").asString());
            us.setBoolean(pos++, json.get("organ_doner_ind").asBoolean());
            us.setString( pos++, json.get("address_line1").asString());
            us.setString( pos++, json.get("address_line2").asString());
            us.setString( pos++, json.get("city").asString());
            us.setString( pos++, json.get("state_code").asString());
            us.setString( pos++, json.get("postal_code").asString());
            us.setString( pos++, json.get("country_code").asString());
            us.setString( pos++, json.get("marital_status_code").asString());
            us.setString( pos++, json.get("occupation_code").asString());
            us.setString( pos++, json.get("nationality").asString());
            us.setString( pos++, json.get("citizen_role_type").asString());
            us.setInt(    pos++, json.get("related_person").asInt());
            us.setString( pos++, json.get("relationship_role_type").asString());
            us.setString( pos++, json.get("birth_place").asString());
            us.setString( pos++, json.get("primary_language_code").asString());
            us.setString( pos++, json.get("race").asString());
            us.setString( pos++, json.get("ethnicity").asString());
            us.setString( pos++, json.get("education_level_code").asString());
            us.setString( pos++, json.get("disability_type_code").asString());
            us.setString( pos++, json.get("living_arrangement_code").asString());
            us.setString( pos++, json.get("religious_affiliation_code").asString());
            us.setString( pos++, json.get("mothers_maiden_name").asString());
            us.setString( pos++, json.get("veterans_status").asString());

            us.setInt(    pos,   json.get("id").asInt());
            us.executeUpdate();

            us.close();
        }
        catch(IOException | SQLException e) {
            log.log(Level.SEVERE, "updatePerson()", e);
            return "{success:false,ex:'update',err:" + e + "}";
        }

        return "{success:true,ex:'update',time:" + (System.currentTimeMillis() - start) + "}";
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="createPerson()">

    final static String PERSON_CREATE =
        "INSERT INTO avrio.person SET " +
        "email=?,version=?,drivers_license_num=?,ssn=?,account_number=?,name_prefix=?," +
        "given_name=?,middle_name=?,family_name=?,name_suffix=?,alias_name=?," +
        "description=?,telephone1=?,telephone2=?,telephone3=?,gender_code=?,birth_time=?," +
        "multiple_birth_ind=?,birth_order=?,deceased_ind=?,deceased_datetime=?,organ_doner_ind=?," +
        "address_line1=?,address_line2=?,city=?,state_code=?,postal_code=?,country_code=?," +
        "marital_status_code=?,occupation_code=?,nationality=?,citizen_role_type=?," +
        "related_person=?,relationship_role_type=?,birth_place=?,primary_language_code=?,race=?," +
        "ethnicity=?,education_level_code=?,disability_type_code=?,living_arrangement_code=?," +
        "religious_affiliation_code=?,mothers_maiden_name=?,veterans_status=?";

    public String createPerson(HttpServletRequest request) {

        long start = System.currentTimeMillis();
        long lastId = 0;
        try (Connection conn = ds.getConnection()) {

            JsonObject json = JsonObject.readFrom(request.getReader());
            System.out.println("createPerson="+json);

            int pos = 1;
            PreparedStatement us = conn.prepareStatement(PERSON_CREATE);
            us.setString( pos++, json.get("email").asString());
            us.setLong(   pos++, json.get("version").asLong());
            us.setString( pos++, json.get("drivers_license_num").asString());
            us.setInt(    pos++, json.get("ssn").asInt());
            us.setString( pos++, json.get("account_number").asString());
            us.setString( pos++, json.get("name_prefix").asString());
            us.setString( pos++, json.get("given_name").asString());
            us.setString( pos++, json.get("middle_name").asString());
            us.setString( pos++, json.get("family_name").asString());
            us.setString( pos++, json.get("name_suffix").asString());
            us.setString( pos++, json.get("alias_name").asString());
            us.setString( pos++, json.get("description").asString());
            us.setString( pos++, json.get("telephone1").asString());
            us.setString( pos++, json.get("telephone2").asString());
            us.setString( pos++, json.get("telephone3").asString());
            us.setString( pos++, json.get("gender_code").asString());
            us.setString( pos++, (json.get("birth_time") == null || json.get("birth_time").isNull()) ? DEFAUL_DATE : json.get("birth_time").asString());
            us.setBoolean(pos++, json.get("multiple_birth_ind").asBoolean());
            us.setInt(    pos++, json.get("birth_order").asInt());
            us.setBoolean(pos++, json.get("deceased_ind").asBoolean());
            us.setString( pos++, (json.get("deceased_datetime") == null || json.get("deceased_datetime").isNull()) ? DEFAUL_DATE : json.get("deceased_datetime").asString());
            us.setBoolean(pos++, json.get("organ_doner_ind").asBoolean());
            us.setString( pos++, json.get("address_line1").asString());
            us.setString( pos++, json.get("address_line2").asString());
            us.setString( pos++, json.get("city").asString());
            us.setString( pos++, json.get("state_code").asString());
            us.setString( pos++, json.get("postal_code").asString());
            us.setString( pos++, json.get("country_code").asString());
            us.setString( pos++, json.get("marital_status_code").asString());
            us.setString( pos++, json.get("occupation_code").asString());
            us.setString( pos++, json.get("nationality").asString());
            us.setString( pos++, json.get("citizen_role_type").asString());
            us.setInt(    pos++, json.get("related_person").asInt());
            us.setString( pos++, json.get("relationship_role_type").asString());
            us.setString( pos++, json.get("birth_place").asString());
            us.setString( pos++, json.get("primary_language_code").asString());
            us.setString( pos++, json.get("race").asString());
            us.setString( pos++, json.get("ethnicity").asString());
            us.setString( pos++, json.get("education_level_code").asString());
            us.setString( pos++, json.get("disability_type_code").asString());
            us.setString( pos++, json.get("living_arrangement_code").asString());
            us.setString( pos++, json.get("religious_affiliation_code").asString());
            us.setString( pos++, json.get("mothers_maiden_name").asString());
            us.setString( pos++, json.get("veterans_status").asString());

            us.executeUpdate();
            //us.close();

            us = conn.prepareStatement("SELECT LAST_INSERT_ID()");
            ResultSet rs = us.executeQuery();
            if(rs.next()) {
                lastId = rs.getInt(1);
            }
            rs.close();
            us.close();
        }
        catch(IOException | SQLException e) {
            log.log(Level.SEVERE, "createPerson()", e);
            return "{success:false,ex:'create',err:" + e + "}";
        }

        return "{success:true,ex:'create',id:" + lastId + ",time:" + (System.currentTimeMillis() - start) + "}";
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="deletePerson()">

    final static String PERSON_DELETE =
        "DELETE FROM avrio.person " +
        "WHERE id=?";

    public String deletePerson(HttpServletRequest request) {

        long start = System.currentTimeMillis();

        try (Connection conn = ds.getConnection()) {

            JsonObject json = JsonObject.readFrom(request.getReader());
            System.out.println("deletePerson="+json);

            PreparedStatement us = conn.prepareStatement(PERSON_DELETE);
            us.setInt(1, json.get("id").asInt());

            us.executeUpdate();
            us.close();
        }
        catch(IOException | SQLException e) {
            log.log(Level.SEVERE, "deletePerson()", e);
            return "{success:false,ex:'delete',err:" + e + "}";
        }

        return "{success:true,ex:'delete',time:" + (System.currentTimeMillis() - start) + "}";
    }
// </editor-fold>
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="GRUD DeviceType Table">
// <editor-fold defaultstate="collapsed" desc="loadDeviceType()">

    final static String DEVICE_TYPE_LOAD =
        "SELECT SQL_CALC_FOUND_ROWS IFNULL( " +
        "CONCAT( " +
        "'{id:',device_type_id," +
        "',version:',version," +
        "',device_type:',QUOTE(device_type)," +
        "',manufacturer_model_name:',QUOTE(manufacturer_model_name)," +
        "',min_bit_rate:',min_bit_rate," +
        "'}'),'{success:false}') json " +
        "FROM avrio.device_type " +
        "ORDER BY device_type_id";

    public String loadDeviceType(int start, int limit, String sort, String dir) {

        long set = System.currentTimeMillis();

        StringBuilder result = new StringBuilder();
        int count = 0;
        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = conn.prepareStatement(DEVICE_TYPE_LOAD + " LIMIT ?,?");
            us.setInt(1, start);
            us.setInt(2, limit);

            ResultSet rs = us.executeQuery();
            for(int idx = 0; rs.next(); idx++) {
		if(idx != 0) result.append(',');
                result.append(rs.getString("json"));
            }
            //rs.close();
            //us.close();

            // items count
            us = conn.prepareStatement("SELECT FOUND_ROWS() cnt");
            rs = us.executeQuery();
            if(rs.next()) {
                count = rs.getInt("cnt");
            }
            rs.close();
            us.close();
        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "loadDeviceType()", e);
            return "{success:false,ex:'load',err:" + e + "}";
        }

        return "{success:true,ex:'load',total:" + count + ",time:" + (System.currentTimeMillis() - set) + ",data:[" + result.toString() + "]}";
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="updateDeviceType()">

    final static String DEVICE_TYPE_UPDATE =
        "UPDATE avrio.device_type SET " +
        "version=?,device_type=?,manufacturer_model_name=?,min_bit_rate=? " +
        "WHERE device_type_id=?";

    public String updateDeviceType(HttpServletRequest request) {

        long start = System.currentTimeMillis();
        try (Connection conn = ds.getConnection()) {

            JsonObject json = JsonObject.readFrom(request.getReader());
            System.out.println("updateDeviceType="+json);

            int pos = 1;
            PreparedStatement us = conn.prepareStatement(DEVICE_TYPE_UPDATE);
            us.setLong(  pos++, json.get("version").asLong());
            us.setString(pos++, json.get("device_type").asString());
            us.setString(pos++, json.get("manufacturer_model_name").asString());
            us.setInt(   pos++, json.get("min_bit_rate").asInt());

            us.setInt(   pos,   json.get("id").asInt());
            us.executeUpdate();

            us.close();
        }
        catch(IOException | SQLException e) {
            log.log(Level.SEVERE, "updateDeviceType()", e);
            return "{success:false,ex:'update',err:" + e + "}";
        }

        return "{success:true,ex:'update',time:" + (System.currentTimeMillis() - start) + "}";
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="createDeviceType()">

    final static String DEVICE_TYPE_CREATE =
        "INSERT INTO avrio.device_type SET " +
        "version=?,device_type=?,manufacturer_model_name=?,min_bit_rate=?";

    public String createDeviceType(HttpServletRequest request) {

        long start = System.currentTimeMillis();
        long lastId = 0;
        try (Connection conn = ds.getConnection()) {

            JsonObject json = JsonObject.readFrom(request.getReader());
            System.out.println("createDeviceType json="+json);

            int pos = 1;
            PreparedStatement us = conn.prepareStatement(DEVICE_TYPE_CREATE);
            us.setLong(  pos++, json.get("version").asLong());
            us.setString(pos++, json.get("device_type").asString());
            us.setString(pos++, json.get("manufacturer_model_name").asString());
            us.setInt(   pos++, json.get("min_bit_rate").asInt());

            us.executeUpdate();
            //us.close();

            us = conn.prepareStatement("SELECT LAST_INSERT_ID()");
            ResultSet rs = us.executeQuery();
            if(rs.next()) {
                lastId = rs.getInt(1);
            }
            rs.close();
            us.close();
        }
        catch(IOException | SQLException e) {
            log.log(Level.SEVERE, "createDeviceType()", e);
            return "{success:false,ex:'create',err:" + e + "}";
        }

        return "{success:true,ex:'create',id:" + lastId + ",time:" + (System.currentTimeMillis() - start) + "}";
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="deleteDeviceType()">

    final static String DEVICE_TYPE_DELETE =
        "DELETE FROM avrio.device_type " +
        "WHERE device_type_id=?";

    public String deleteDeviceType(HttpServletRequest request) {

        long start = System.currentTimeMillis();
        try (Connection conn = ds.getConnection()) {

            JsonObject json = JsonObject.readFrom(request.getReader());
            System.out.println("deleteDeviceType="+json);

            PreparedStatement us = conn.prepareStatement(DEVICE_TYPE_DELETE);
            us.setInt(1, json.get("id").asInt());

            us.executeUpdate();
            us.close();
        }
        catch(IOException | SQLException e) {
            log.log(Level.SEVERE, "deleteDeviceType()", e);
            return "{success:false,ex:'delete',err:" + e + "}";
        }

        return "{success:true,ex:'delete',time:" + (System.currentTimeMillis() - start) + "}";
    }
// </editor-fold>
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="GRUD DeviceSession Table">
// <editor-fold defaultstate="collapsed" desc="loadDeviceSession()">

    final static String DEVICE_SESSION_LOAD =
        "SELECT SQL_CALC_FOUND_ROWS IFNULL( " +
        "CONCAT( " +
        "'{id:',device_session_id," +
        "',version:',version," +
        "',device_type_id:',device_type_id," +
        "',session_id:',session_id," +
        "',device_status:',QUOTE(device_status)," +
        "',measurement_sample_rate:',measurement_sample_rate," +
        "',facility_id:',facility_id," +
        "',device_start_time:',QUOTE(IFNULL(device_start_time,''))," +
        "',device_stop_time:',QUOTE(IFNULL(device_stop_time,''))," +
        "',device_serial_number:',QUOTE(device_serial_number)," +
        "',device_inactivity_timeout:',device_inactivity_timeout," +
        "'}'),'{success:false}') json " +
        "FROM avrio.device_session " +
        "ORDER BY device_session_id";

    public String loadDeviceSession(int start, int limit, String sort, String dir) {

        long set = System.currentTimeMillis();

        StringBuilder result = new StringBuilder();
        int count = 0;
        try(Connection conn = ds.getConnection()) {

            PreparedStatement us = conn.prepareStatement(DEVICE_SESSION_LOAD + " LIMIT ?,?");
            us.setInt(1, start);
            us.setInt(2, limit);

            ResultSet rs = us.executeQuery();
            for(int idx = 0; rs.next(); idx++) {
		if(idx != 0) result.append(',');
                result.append(rs.getString("json"));
            }
            //rs.close();
            //us.close();

            // items count
            us = conn.prepareStatement("SELECT FOUND_ROWS() cnt");
            rs = us.executeQuery();
            if(rs.next()) {
                count = rs.getInt("cnt");
            }
            rs.close();
            us.close();
        }
        catch(SQLException e) {
            log.log(Level.SEVERE, "loadDeviceSession()", e);
            return "{success:false,ex:'load',err:" + e + "}";
        }

        return "{success:true,ex:'load',total:" + count + ",time:" + (System.currentTimeMillis() - set) + ",data:[" + result.toString() + "]}";
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="updateDeviceSession()">

    final static String DEVICE_SESSION_UPDATE =
        "UPDATE avrio.device_session SET " +
        "version=?,device_type_id=?,session_id=?,device_status=?,measurement_sample_rate=?," +
        "facility_id=?,device_start_time=?,device_stop_time=?,device_serial_number=?,device_inactivity_timeout=? " +
        "WHERE device_session_id=?";

    public String updateDeviceSession(HttpServletRequest request) {

        long start = System.currentTimeMillis();
        try (Connection conn = ds.getConnection()) {

            JsonObject json = JsonObject.readFrom(request.getReader());
            System.out.println("updateDeviceSession="+json);

            int pos = 1;
            PreparedStatement us = conn.prepareStatement(DEVICE_SESSION_UPDATE);
            us.setLong(  pos++, json.get("version").asLong());
            us.setInt(   pos++, json.get("device_type_id").asInt());
            us.setInt(   pos++, json.get("session_id").asInt());
            us.setString(pos++, json.get("device_status").asString());
            us.setInt(   pos++, json.get("measurement_sample_rate").asInt());
            us.setInt(   pos++, json.get("facility_id").asInt());
            us.setString(pos++, (json.get("device_start_time") == null || json.get("device_start_time").isNull()) ? null : json.get("device_start_time").asString());
            us.setString(pos++, (json.get("device_stop_time") == null || json.get("device_stop_time").isNull()) ? null : json.get("device_stop_time").asString());
            us.setString(pos++, json.get("device_serial_number").asString());
            us.setInt(   pos++, json.get("device_inactivity_timeout").asInt());

            us.setInt(   pos,   json.get("id").asInt());
            us.executeUpdate();

            us.close();
        }
        catch(IOException | SQLException e) {
            log.log(Level.SEVERE, "updateDeviceSession()", e);
            return "{success:false,ex:'update',err:" + e + "}";
        }

        return "{success:true,ex:'update',time:" + (System.currentTimeMillis() - start) + "}";
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="createDeviceSession()">

    final static String DEVICE_SESSION_CREATE =
        "INSERT INTO avrio.device_session SET " +
        "version=?,device_type_id=?,session_id=?,device_status=?,measurement_sample_rate=?," +
        "facility_id=?,device_start_time=?,device_stop_time=?,device_serial_number=?,device_inactivity_timeout=?";

    public String createDeviceSession(HttpServletRequest request) {

        long start = System.currentTimeMillis();
        long lastId = 0;
        try (Connection conn = ds.getConnection()) {

            JsonObject json = JsonObject.readFrom(request.getReader());
            System.out.println("createDeviceSession json="+json);

            int pos = 1;
            PreparedStatement us = conn.prepareStatement(DEVICE_SESSION_CREATE);
            us.setLong(  pos++, json.get("version").asLong());
            us.setInt(   pos++, json.get("device_type_id").asInt());
            us.setInt(   pos++, json.get("session_id").asInt());
            us.setString(pos++, json.get("device_status").asString());
            us.setInt(   pos++, json.get("measurement_sample_rate").asInt());
            us.setInt(   pos++, json.get("facility_id").asInt());
            us.setString(pos++, (json.get("device_start_time") == null || json.get("device_start_time").isNull()) ? null : json.get("device_start_time").asString());
            us.setString(pos++, (json.get("device_stop_time") == null || json.get("device_stop_time").isNull()) ? null : json.get("device_stop_time").asString());
            us.setString(pos++, json.get("device_serial_number").asString());
            us.setInt(   pos++, json.get("device_inactivity_timeout").asInt());

            us.executeUpdate();
            //us.close();

            us = conn.prepareStatement("SELECT LAST_INSERT_ID()");
            ResultSet rs = us.executeQuery();
            if(rs.next()) {
                lastId = rs.getInt(1);
            }
            rs.close();
            us.close();
        }
        catch(IOException | SQLException e) {
            log.log(Level.SEVERE, "createDeviceSession()", e);
            return "{success:false,ex:'create',err:" + e + "}";
        }

        return "{success:true,ex:'create',id:" + lastId + ",time:" + (System.currentTimeMillis() - start) + "}";
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="deleteDeviceSession()">

    final static String DEVICE_SESSION_DELETE =
        "DELETE FROM avrio.device_session " +
        "WHERE device_session_id=?";

    public String deleteDeviceSession(HttpServletRequest request) {

        long start = System.currentTimeMillis();
        try (Connection conn = ds.getConnection()) {

            JsonObject json = JsonObject.readFrom(request.getReader());
            System.out.println("deleteDeviceSession="+json);

            PreparedStatement us = conn.prepareStatement(DEVICE_SESSION_DELETE);
            us.setInt(1, json.get("id").asInt());

            us.executeUpdate();
            us.close();
        }
        catch(IOException | SQLException e) {
            log.log(Level.SEVERE, "deleteDeviceSession()", e);
            return "{success:false,ex:'delete',err:" + e + "}";
        }

        return "{success:true,ex:'delete',time:" + (System.currentTimeMillis() - start) + "}";
    }
// </editor-fold>
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getString()">

    public String getString(String string, String defStr, int limit) {

        if(string == null || string.length() == 0) return defStr;
        int maxLength = (string.length() < limit) ? string.length() : limit;
        return string.substring(0, maxLength);
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getInt()">

    public int getInt(String value, int defInt) {

        int integer;
        try {
            integer = Integer.parseInt(value);
        }
        catch(NumberFormatException ignore) {
            integer = defInt;
        }
        return integer;
    }
// </editor-fold>

}
